﻿/********************************************************************************************************************
* Author:	Aris Michail																							*
* Email:	std03055@di.uoa.gr																						*
* Purpose:	This script is used in order to create the stored procedure "usp_addattributetocategory" that adds		*
*			an attribute to a category.																				*
********************************************************************************************************************/

CREATE PROCEDURE [dbo].[usp_addattributetocategory]
	@categoryid bigint, 
	@attrname nvarchar(50),
	@attrtype char(1),
	@attrbestvalue char(3)	
AS
	DECLARE entity_cursor CURSOR
	FOR
		SELECT Entities.ID
		FROM Entities
		WHERE Entities.Category = @categoryid
	DECLARE @attrid bigint
	DECLARE @entityid bigint

	INSERT INTO Attributes											/* add the attribute to the category */
	VALUES(@categoryid,@attrname,@attrtype,@attrbestvalue)

	SELECT @attrid = Attributes.ID									/* get the id of the new attribute */
	FROM Attributes
	WHERE Attributes.Category = @categoryid and Attributes.Name = @attrname

	OPEN entity_cursor												/* add the attribute to all entities in */
	FETCH NEXT FROM entity_cursor									/* category with value 'N/A' */
	INTO @entityid
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO [Entity-Attribute]
			VALUES(@entityid,@attrid,'N/A')

			FETCH NEXT FROM entity_cursor
			INTO @entityid
		END
	CLOSE entity_cursor
		DEALLOCATE entity_cursor

RETURN 0